package com.apes.fn.scm.retailOrder.repository;

import com.apes.fn.scm.retailOrder.model.RetailOrderPayMethod;
import com.apes.framework.jpa.repository.CustomRepository;
import io.lettuce.core.dynamic.annotation.Param;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author HuJianlin
 * @create 2018-10-31 16:07
 */
public interface RetailOrderPayMethodRepository extends CustomRepository<RetailOrderPayMethod, String> {


    @Query("SELECT T FROM RetailOrderPayMethod T WHERE T.order.id = :id AND T.paymentMethodType.id = '05' ")
    RetailOrderPayMethod findVipSettlePayMethod(@Param("id") String id);


    @Query(value = "SELECT A.CUSTOMER_ID, SUM(AMOUNT) AMT FROM RETAIL_ORDER_PAYMENT_METHOD A " +
            "WHERE A.RETAIL_ORDER_ID = :retailId AND A.PAYMENT_METHOD_TYPE = '09' GROUP BY A.CUSTOMER_ID", nativeQuery = true)
    List<Map<String, Object>> findByCustomerSumAmt(@Param("retailId") String retailId);

    /**
     * fetch data by partner Id and payment Method Type id and start date and end Date
     *
     * @param reconcileBeginDate start date
     * @param reconcileEndDate   end Date
     * @param partnerId          partner id
     * @param typeId             payment Method Type id
     * @param pageable           pageable
     * @return Page<Map < String, Object>>
     */
    @Query(value = "SELECT AMOUNT, ID, PAYMENT_METHOD_TYPE, COMPANY_ID, TO_CHAR(DATE_APPROVE,'yyyy-mm-dd') DATEDONE, DEPT,\n" +
            "       CARLICENS, CUSTOMER,FROMORDER  \n" +
            "  FROM (SELECT M.AMOUNT AS AMOUNT, O.ID AS ID, M.PAYMENT_METHOD_TYPE,\n" +
            "                O.COMPANY_ID, O.DATE_APPROVE,\n" +
            "                (SELECT NAME FROM MAS_DEPT WHERE ID = O.DEPT_ID) AS DEPT,\n" +
            "                (SELECT CAR_LICENSE\n" +
            "                    FROM FN_CUSTOMER_EXPAND\n" +
            "                   WHERE ID = O.CUSTOMER_EXPAND_ID) AS CARLICENS,\n" +
            "                TO_CHAR(O.DATE_APPROVE, 'YYYY-MM-DD') AS DATEDONE,\n" +
            "                (SELECT C.NAME\n" +
            "                    FROM MAS_PARTY_ROLE B, MAS_PARTY C\n" +
            "                   WHERE O.CLERK_ID = B.ID\n" +
            "                     AND B.PARTY_ID = C.ID) AS CUSTOMER,'零售单' FROMORDER ,M.CUSTOMER_ID \n" +
            "           FROM RETAIL_ORDER_PAYMENT_METHOD M, RETAIL_ORDER O\n" +
            "          WHERE M.RETAIL_ORDER_ID = O.ID\n" +
            "            AND O.STATE IN ('done','confirmed') \n" +
            "         UNION ALL\n" +
            "         SELECT M.AMOUNT AS AMOUNT, O.ID AS ID, M.PAYMENT_METHOD_TYPE,\n" +
            "                O.COMPANY_ID, O.DATE_APPROVE,\n" +
            "                (SELECT NAME FROM MAS_DEPT WHERE ID = O.DEPT_ID) AS DEPT,\n" +
            "                (SELECT CAR_LICENSE\n" +
            "                    FROM FN_CUSTOMER_EXPAND\n" +
            "                   WHERE ID = O.CUSTOMER_EXPAND_ID) AS CARLICENS,\n" +
            "                TO_CHAR(O.DATE_APPROVE, 'YYYY-MM-DD') AS DATEDONE,\n" +
            "                'APP' AS CUSTOMER,'订金单' FROMORDER ,M.CUSTOMER_ID \n" +
            "           FROM ADVANCE_PAYMENT_METHOD M, ADVANCE_PAYMENT O\n" +
            "          WHERE O.ID = M.PAYMENT_ID\n" +
            "            AND M.PAYMENT_ID = O.ID\n" +
            "            AND O.STATE = 'approve')\n" +
            " WHERE 1 = 1  " +
            "   AND (CUSTOMER_ID LIKE '%K%' AND CUSTOMER_ID <> 'K1001') " +
            "   AND COMPANY_ID = ?1\n" +
            "   AND PAYMENT_METHOD_TYPE = ?2 \n" +
            "   AND GET_DATE(DATE_APPROVE) >= ?3 \n" +
            "   AND GET_DATE(DATE_APPROVE) <= ?4 ORDER BY ID ",
            countQuery = "SELECT COUNT(*)\n" +
                    "  FROM (SELECT M.AMOUNT AS AMOUNT, O.ID AS ID, M.PAYMENT_METHOD_TYPE,\n" +
                    "                O.COMPANY_ID, O.DATE_APPROVE,\n" +
                    "                (SELECT NAME FROM MAS_DEPT WHERE ID = O.DEPT_ID) AS DEPT,\n" +
                    "                (SELECT CAR_LICENSE\n" +
                    "                    FROM FN_CUSTOMER_EXPAND\n" +
                    "                   WHERE ID = O.CUSTOMER_EXPAND_ID) AS CARLICENS,\n" +
                    "                TO_CHAR(O.DATE_APPROVE, 'YYYY-MM-DD') AS DATEDONE,\n" +
                    "                (SELECT C.NAME\n" +
                    "                    FROM MAS_PARTY_ROLE B, MAS_PARTY C\n" +
                    "                   WHERE M.customer_id = B.ID\n" +
                    "                     AND B.PARTY_ID = C.ID) AS CUSTOMER,'零售单' FROMORDER ,M.CUSTOMER_ID \n" +
                    "           FROM RETAIL_ORDER_PAYMENT_METHOD M, RETAIL_ORDER O\n" +
                    "          WHERE M.RETAIL_ORDER_ID = O.ID\n" +
                    "            AND O.STATE IN ('done','confirmed') \n" +
                    "         UNION ALL\n" +
                    "         SELECT M.AMOUNT AS AMOUNT, O.ID AS ID, M.PAYMENT_METHOD_TYPE,\n" +
                    "                O.COMPANY_ID, O.DATE_APPROVE,\n" +
                    "                (SELECT NAME FROM MAS_DEPT WHERE ID = O.DEPT_ID) AS DEPT,\n" +
                    "                (SELECT CAR_LICENSE\n" +
                    "                    FROM FN_CUSTOMER_EXPAND\n" +
                    "                   WHERE ID = O.CUSTOMER_EXPAND_ID) AS CARLICENS,\n" +
                    "                TO_CHAR(O.DATE_APPROVE, 'YYYY-MM-DD') AS DATEDONE,\n" +
                    "                (SELECT C.NAME\n" +
                    "                    FROM MAS_PARTY_ROLE B, MAS_PARTY C\n" +
                    "                   WHERE M.customer_id = B.ID\n" +
                    "                     AND B.PARTY_ID = C.ID) AS CUSTOMER,'订金单' FROMORDER,M.CUSTOMER_ID \n" +
                    "           FROM ADVANCE_PAYMENT_METHOD M, ADVANCE_PAYMENT O\n" +
                    "          WHERE O.ID = M.PAYMENT_ID\n" +
                    "            AND M.PAYMENT_ID = O.ID\n" +
                    "            AND O.STATE = 'approve')\n" +
                    " WHERE 1 = 1\n" +
                    "   AND (CUSTOMER_ID LIKE '%K%'  AND  CUSTOMER_ID <> 'K1001') " +
                    "   AND COMPANY_ID = ?1 \n" +
                    "   AND PAYMENT_METHOD_TYPE = ?2 \n" +
                    "   AND GET_DATE(DATE_APPROVE) >= ?3 \n" +
                    "   AND GET_DATE(DATE_APPROVE) <= ?4 ORDER BY ID \n",
            nativeQuery = true
    )
    Page<Map> findByPartnerIdAndType(String partnerId, String typeId, Date reconcileBeginDate, Date reconcileEndDate, Pageable pageable);



    /**
     * fetch data by partner Id and payment Method Type id and start date and end Date
     *
     * @param reconcileBeginDate start date
     * @param reconcileEndDate   end Date
     * @param partnerId          partner id
     * @param typeId             payment Method Type id
     * @param pageable           pageable
     * @return Page<Map < String, Object>>
     */
    @Query(value = "SELECT AMOUNT, ID, PAYMENT_METHOD_TYPE, COMPANY_ID, TO_CHAR(DATE_APPROVE,'yyyy-mm-dd') DATEDONE, DEPT,\n" +
            "       CARLICENS, CUSTOMER,FROMORDER,CUSTOMER_ID  \n" +
            "  FROM (SELECT M.AMOUNT AS AMOUNT, O.ID AS ID, M.PAYMENT_METHOD_TYPE,\n" +
            "                O.COMPANY_ID, O.DATE_APPROVE,\n" +
            "                (SELECT NAME FROM MAS_DEPT WHERE ID = O.DEPT_ID) AS DEPT,\n" +
            "                (SELECT CAR_LICENSE\n" +
            "                    FROM FN_CUSTOMER_EXPAND\n" +
            "                   WHERE ID = O.CUSTOMER_EXPAND_ID) AS CARLICENS,\n" +
            "                TO_CHAR(O.DATE_APPROVE, 'YYYY-MM-DD') AS DATEDONE,\n" +
            "                (SELECT C.NAME\n" +
            "                    FROM MAS_PARTY_ROLE B, MAS_PARTY C\n" +
            "                   WHERE O.CLERK_ID = B.ID\n" +
            "                     AND B.PARTY_ID = C.ID) AS CUSTOMER,'零售单' FROMORDER ,M.CUSTOMER_ID \n" +
            "           FROM RETAIL_ORDER_PAYMENT_METHOD M, RETAIL_ORDER O\n" +
            "          WHERE M.RETAIL_ORDER_ID = O.ID\n" +
            "            AND O.STATE IN ('done','confirmed') \n" +
            "         UNION ALL\n" +
            "         SELECT M.AMOUNT AS AMOUNT, O.ID AS ID, M.PAYMENT_METHOD_TYPE,\n" +
            "                O.COMPANY_ID, O.DATE_APPROVE,\n" +
            "                (SELECT NAME FROM MAS_DEPT WHERE ID = O.DEPT_ID) AS DEPT,\n" +
            "                (SELECT CAR_LICENSE\n" +
            "                    FROM FN_CUSTOMER_EXPAND\n" +
            "                   WHERE ID = O.CUSTOMER_EXPAND_ID) AS CARLICENS,\n" +
            "                TO_CHAR(O.DATE_APPROVE, 'YYYY-MM-DD') AS DATEDONE,\n" +
            "                'APP' AS CUSTOMER,'订金单' FROMORDER ,M.CUSTOMER_ID \n" +
            "           FROM ADVANCE_PAYMENT_METHOD M, ADVANCE_PAYMENT O\n" +
            "          WHERE O.ID = M.PAYMENT_ID\n" +
            "            AND M.PAYMENT_ID = O.ID\n" +
            "            AND O.STATE = 'approve')\n" +
            " WHERE 1 = 1  " +
            "   AND ((PAYMENT_METHOD_TYPE <> '09') OR (PAYMENT_METHOD_TYPE = '09' AND CUSTOMER_ID = 'K1001')) " +
            "   AND COMPANY_ID = ?1\n" +
            "   AND PAYMENT_METHOD_TYPE = ?2 \n" +
            "   AND GET_DATE(DATE_APPROVE) >= ?3 \n" +
            "   AND GET_DATE(DATE_APPROVE) <= ?4  ORDER BY ID",
            countQuery = "SELECT COUNT(*)\n" +
                    "  FROM (SELECT M.AMOUNT AS AMOUNT, O.ID AS ID, M.PAYMENT_METHOD_TYPE,\n" +
                    "                O.COMPANY_ID, O.DATE_APPROVE,\n" +
                    "                (SELECT NAME FROM MAS_DEPT WHERE ID = O.DEPT_ID) AS DEPT,\n" +
                    "                (SELECT CAR_LICENSE\n" +
                    "                    FROM FN_CUSTOMER_EXPAND\n" +
                    "                   WHERE ID = O.CUSTOMER_EXPAND_ID) AS CARLICENS,\n" +
                    "                TO_CHAR(O.DATE_APPROVE, 'YYYY-MM-DD') AS DATEDONE,\n" +
                    "                (SELECT C.NAME\n" +
                    "                    FROM MAS_PARTY_ROLE B, MAS_PARTY C\n" +
                    "                   WHERE M.customer_id = B.ID\n" +
                    "                     AND B.PARTY_ID = C.ID) AS CUSTOMER,'零售单' FROMORDER ,M.CUSTOMER_ID \n" +
                    "           FROM RETAIL_ORDER_PAYMENT_METHOD M, RETAIL_ORDER O\n" +
                    "          WHERE M.RETAIL_ORDER_ID = O.ID\n" +
                    "            AND O.STATE  IN ('done','confirmed')  \n" +
                    "         UNION ALL\n" +
                    "         SELECT M.AMOUNT AS AMOUNT, O.ID AS ID, M.PAYMENT_METHOD_TYPE,\n" +
                    "                O.COMPANY_ID, O.DATE_APPROVE,\n" +
                    "                (SELECT NAME FROM MAS_DEPT WHERE ID = O.DEPT_ID) AS DEPT,\n" +
                    "                (SELECT CAR_LICENSE\n" +
                    "                    FROM FN_CUSTOMER_EXPAND\n" +
                    "                   WHERE ID = O.CUSTOMER_EXPAND_ID) AS CARLICENS,\n" +
                    "                TO_CHAR(O.DATE_APPROVE, 'YYYY-MM-DD') AS DATEDONE,\n" +
                    "                (SELECT C.NAME\n" +
                    "                    FROM MAS_PARTY_ROLE B, MAS_PARTY C\n" +
                    "                   WHERE M.customer_id = B.ID\n" +
                    "                     AND B.PARTY_ID = C.ID) AS CUSTOMER,'订金单' FROMORDER,M.CUSTOMER_ID \n" +
                    "           FROM ADVANCE_PAYMENT_METHOD M, ADVANCE_PAYMENT O\n" +
                    "          WHERE O.ID = M.PAYMENT_ID\n" +
                    "            AND M.PAYMENT_ID = O.ID\n" +
                    "            AND O.STATE = 'approve')\n" +
                    " WHERE 1 = 1\n" +
                    "   AND ((PAYMENT_METHOD_TYPE <> '09') OR (PAYMENT_METHOD_TYPE = '09' AND CUSTOMER_ID = 'K1001')) " +
                    "   AND COMPANY_ID = ?1 \n" +
                    "   AND PAYMENT_METHOD_TYPE = ?2 \n" +
                    "   AND GET_DATE(DATE_APPROVE) >= ?3 \n" +
                    "   AND GET_DATE(DATE_APPROVE) <= ?4 ORDER BY ID \n",
            nativeQuery = true
    )
    Page<Map> findByPartnerIdAndTypeForJequn(String partnerId, String typeId, Date reconcileBeginDate, Date reconcileEndDate, Pageable pageable);







    /**
     * fetch data by partner Id and payment Method Type id and start date and end Date
     *
     * @param reconcileBeginDate start date
     * @param reconcileEndDate   end Date
     * @param partnerId          partner id
     * @param typeId             payment Method Type id
     * @param pageable           pageable
     * @return Page<Map < String, Object>>
     */
    @Query(value = "SELECT AMOUNT, ID, PAYMENT_METHOD_TYPE, COMPANY_ID, TO_CHAR(DATE_APPROVE,'yyyy-mm-dd') DATEDONE, DEPT,\n" +
            "       CARLICENS, CUSTOMER,FROMORDER  \n" +
            "  FROM (SELECT M.AMOUNT AS AMOUNT, O.ID AS ID, M.PAYMENT_METHOD_TYPE,\n" +
            "                O.COMPANY_ID, O.DATE_APPROVE,\n" +
            "                (SELECT NAME FROM MAS_DEPT WHERE ID = O.DEPT_ID) AS DEPT,\n" +
            "                (SELECT CAR_LICENSE\n" +
            "                    FROM FN_CUSTOMER_EXPAND\n" +
            "                   WHERE ID = O.CUSTOMER_EXPAND_ID) AS CARLICENS,\n" +
            "                TO_CHAR(O.DATE_APPROVE, 'YYYY-MM-DD') AS DATEDONE,\n" +
            "                (SELECT C.NAME\n" +
            "                    FROM MAS_PARTY_ROLE B, MAS_PARTY C\n" +
            "                   WHERE O.CLERK_ID = B.ID\n" +
            "                     AND B.PARTY_ID = C.ID) AS CUSTOMER,'零售单' FROMORDER ,M.CUSTOMER_ID \n" +
            "           FROM RETAIL_ORDER_PAYMENT_METHOD M, RETAIL_ORDER O\n" +
            "          WHERE M.RETAIL_ORDER_ID = O.ID\n" +
            "            AND O.STATE  IN ('done','confirmed')  \n" +
            "         UNION ALL\n" +
            "         SELECT M.AMOUNT AS AMOUNT, O.ID AS ID, M.PAYMENT_METHOD_TYPE,\n" +
            "                O.COMPANY_ID, O.DATE_APPROVE,\n" +
            "                (SELECT NAME FROM MAS_DEPT WHERE ID = O.DEPT_ID) AS DEPT,\n" +
            "                (SELECT CAR_LICENSE\n" +
            "                    FROM FN_CUSTOMER_EXPAND\n" +
            "                   WHERE ID = O.CUSTOMER_EXPAND_ID) AS CARLICENS,\n" +
            "                TO_CHAR(O.DATE_APPROVE, 'YYYY-MM-DD') AS DATEDONE,\n" +
            "                'APP' AS CUSTOMER,'订金单' FROMORDER ,M.CUSTOMER_ID \n" +
            "           FROM ADVANCE_PAYMENT_METHOD M, ADVANCE_PAYMENT O\n" +
            "          WHERE O.ID = M.PAYMENT_ID\n" +
            "            AND M.PAYMENT_ID = O.ID\n" +
            "            AND O.STATE = 'approve')\n" +
            " WHERE 1 = 1  " +
            "   AND CUSTOMER_ID NOT LIKE '%K%' \n" +
            "   AND COMPANY_ID = ?1 \n" +
            "   AND PAYMENT_METHOD_TYPE = ?2 \n" +
            "   AND GET_DATE(DATE_APPROVE) >= ?3 \n" +
            "   AND GET_DATE(DATE_APPROVE) <= ?4 ORDER BY ID ",
            countQuery = "SELECT COUNT(*)\n" +
                    "  FROM (SELECT M.AMOUNT AS AMOUNT, O.ID AS ID, M.PAYMENT_METHOD_TYPE,\n" +
                    "                O.COMPANY_ID, O.DATE_APPROVE,\n" +
                    "                (SELECT NAME FROM MAS_DEPT WHERE ID = O.DEPT_ID) AS DEPT,\n" +
                    "                (SELECT CAR_LICENSE\n" +
                    "                    FROM FN_CUSTOMER_EXPAND\n" +
                    "                   WHERE ID = O.CUSTOMER_EXPAND_ID) AS CARLICENS,\n" +
                    "                TO_CHAR(O.DATE_APPROVE, 'YYYY-MM-DD') AS DATEDONE,\n" +
                    "                (SELECT C.NAME\n" +
                    "                    FROM MAS_PARTY_ROLE B, MAS_PARTY C\n" +
                    "                   WHERE M.customer_id = B.ID\n" +
                    "                     AND B.PARTY_ID = C.ID) AS CUSTOMER,'零售单' FROMORDER ,M.CUSTOMER_ID \n" +
                    "           FROM RETAIL_ORDER_PAYMENT_METHOD M, RETAIL_ORDER O\n" +
                    "          WHERE M.RETAIL_ORDER_ID = O.ID\n" +
                    "            AND O.STATE  IN ('done','confirmed') \n" +
                    "         UNION ALL\n" +
                    "         SELECT M.AMOUNT AS AMOUNT, O.ID AS ID, M.PAYMENT_METHOD_TYPE,\n" +
                    "                O.COMPANY_ID, O.DATE_APPROVE,\n" +
                    "                (SELECT NAME FROM MAS_DEPT WHERE ID = O.DEPT_ID) AS DEPT,\n" +
                    "                (SELECT CAR_LICENSE\n" +
                    "                    FROM FN_CUSTOMER_EXPAND\n" +
                    "                   WHERE ID = O.CUSTOMER_EXPAND_ID) AS CARLICENS,\n" +
                    "                TO_CHAR(O.DATE_APPROVE, 'YYYY-MM-DD') AS DATEDONE,\n" +
                    "                (SELECT C.NAME\n" +
                    "                    FROM MAS_PARTY_ROLE B, MAS_PARTY C\n" +
                    "                   WHERE M.customer_id = B.ID\n" +
                    "                     AND B.PARTY_ID = C.ID) AS CUSTOMER,'订金单' FROMORDER,M.CUSTOMER_ID \n" +
                    "           FROM ADVANCE_PAYMENT_METHOD M, ADVANCE_PAYMENT O\n" +
                    "          WHERE O.ID = M.PAYMENT_ID\n" +
                    "            AND M.PAYMENT_ID = O.ID\n" +
                    "            AND O.STATE = 'approve')\n" +
                    " WHERE 1 = 1\n" +
                    "   AND CUSTOMER_ID NOT LIKE '%K%' " +
                    "   AND COMPANY_ID = ?1 \n" +
                    "   AND PAYMENT_METHOD_TYPE = ?2 \n" +
                    "   AND GET_DATE(DATE_APPROVE) >= ?3 \n" +
                    "   AND GET_DATE(DATE_APPROVE) <= ?4 ORDER BY ID \n",
            nativeQuery = true
    )
    Page<Map> findByPartnerIdAndTypeTwo(String partnerId, String typeId, Date reconcileBeginDate, Date reconcileEndDate, Pageable pageable);

    RetailOrderPayMethod findByOrderId(String id);

    @Query(value = "SELECT B.*\n" +
            "  FROM RETAIL_ORDER A, RETAIL_ORDER_PAYMENT_METHOD B\n" +
            " WHERE A.ID = B.RETAIL_ORDER_ID\n" +
            "   AND B.CUSTOMER_ID IS NOT NULL\n" +
            "   AND B.PAYMENT_METHOD_TYPE = '09'\n" +
            "   AND B.AMOUNT != 0\n" +
            "   AND B.PLATFORM_MANAGEMENT_FEE != 0\n" +
            "   AND B.CUSTOMER_ID != 'C0005998'\n" +
            "   AND B.CUSTOMER_ID != '02532856'\n" +
            "   AND B.CUSTOMER_ID != '02533043'\n" +
            "   AND (((SELECT T.ID\n" +
            "            FROM MAS_PARTY_ROLE T\n" +
            "           WHERE T.PARTY_ID = (SELECT T.PARTY_ID\n" +
            "                                 FROM MAS_PARTY_ROLE T\n" +
            "                                WHERE T.ID = B.CUSTOMER_ID)\n" +
            "             AND T.ROLE_ID = 'Company') IS NULL OR\n" +
            "       (SELECT T.PARTY_ID FROM MAS_PARTY_ROLE T WHERE T.ID = A.COMPANY_ID) =\n" +
            "       (SELECT T.PARTY_ID FROM MAS_PARTY_ROLE T WHERE T.ID = B.CUSTOMER_ID) OR\n" +
            "       (A.ORDER_TYPE != 'replacement' AND B.CUSTOMER_ID != 'K1001' AND\n" +
            "       B.CUSTOMER_ID != 'K1003')) OR\n" +
            "       (B.CUSTOMER_ID IN ('K1001', 'K1003') AND\n" +
            "       A.ORDER_TYPE = 'replacement' AND\n" +
            "       ((SELECT T.PARTY_ID FROM MAS_PARTY_ROLE T WHERE T.ID = A.COMPANY_ID) =\n" +
            "       (SELECT T.PARTY_ID\n" +
            "             FROM MAS_PARTY_ROLE T\n" +
            "            WHERE T.ID = B.CUSTOMER_ID) OR\n" +
            "       (SELECT T.ID\n" +
            "             FROM MAS_PARTY_ROLE T\n" +
            "            WHERE T.PARTY_ID = (SELECT T.PARTY_ID\n" +
            "                                  FROM MAS_PARTY_ROLE T\n" +
            "                                 WHERE T.ID = B.CUSTOMER_ID)\n" +
            "              AND T.ROLE_ID = 'Company') IS NULL)))\n" +
            "   AND A.ID = :id",nativeQuery = true)
    List<RetailOrderPayMethod> findByYSZKW(@Param("id") String id);

    @Query(value = "SELECT B.*\n" +
            "  FROM RETAIL_ORDER A, RETAIL_ORDER_PAYMENT_METHOD B\n" +
            " WHERE A.ID = B.RETAIL_ORDER_ID\n" +
            "   AND B.PLATFORM_MANAGEMENT_FEE != 0\n" +
            "   AND B.CUSTOMER_ID IS NOT NULL\n" +
            "   AND B.PAYMENT_METHOD_TYPE = '09'\n" +
            "   AND ((B.CUSTOMER_ID IN\n" +
            "       ('K1001', 'K1003', 'C0005998', '02532856', '02533043') AND\n" +
            "       A.ORDER_TYPE != 'replacement' AND\n" +
            "       (SELECT T.PARTY_ID FROM MAS_PARTY_ROLE T WHERE T.ID = A.COMPANY_ID) !=\n" +
            "       (SELECT T.PARTY_ID FROM MAS_PARTY_ROLE T WHERE T.ID = B.CUSTOMER_ID)) OR\n" +
            "       ((SELECT T.ID\n" +
            "            FROM MAS_PARTY_ROLE T\n" +
            "           WHERE T.PARTY_ID = (SELECT T.PARTY_ID\n" +
            "                                 FROM MAS_PARTY_ROLE T\n" +
            "                                WHERE T.ID = B.CUSTOMER_ID)\n" +
            "             AND T.ROLE_ID = 'Company') IS NOT NULL AND\n" +
            "       A.ORDER_TYPE = 'replacement' AND\n" +
            "       (SELECT T.PARTY_ID FROM MAS_PARTY_ROLE T WHERE T.ID = A.COMPANY_ID) !=\n" +
            "       (SELECT T.PARTY_ID FROM MAS_PARTY_ROLE T WHERE T.ID = B.CUSTOMER_ID)))\n" +
            "   AND A.ID = :id",nativeQuery = true)
    List<RetailOrderPayMethod> findByJQYL(@Param("id") String id);
}
